using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using QLVMBAYDomain;
using System.Configuration;
using System.Data.SqlClient;
namespace QLVMBAYDatacess
{
    public class DatchoDA : DataAccessBase
    {
		#region Constructors
		
		public DatchoDA() : base()
        {
			_selectSql = "Select * From DATCHO";
            _insertSql = "Insert Into DATCHO(MADATCHO,MAVE,MAHK,MACTCBAY,SLVE,TRANGTHAI,PHIHUY,TONGTIEN,LOAIDATCHO,LUOTDATCHO) Values (@MADATCHO,@MAVE,@MAHK,@MACTCBAY,@SLVE,@TRANGTHAI,@PHIHUY,@TONGTIEN,@LOAIDATCHO,@LUOTDATCHO)";
            _updateSql = "Update DATCHO Set MAHK=@MAHK,MACTCBAY=@MACTCBAY,SLVE=@SLVE,TRANGTHAI=@TRANGTHAI,PHIHUY=@PHIHUY,TONGTIEN=@TONGTIEN,LOAIDATCHO=@LOAIDATCHO,LUOTDATCHO=@LUOTDATCHO Where 1=1 And MADATCHO=@MADATCHO";
        	_deleteSql = "Delete From DATCHO Where 1=1 And MADATCHO=@MADATCHO";
        	_primarSql = "Select * From DATCHO Where 1=1 And MADATCHO=@MADATCHO";
        }
		
		protected static DatchoDA _inst = null;
		public static DatchoDA Inst
		{
			get
			{
				if(_inst == null)
					_inst = new DatchoDA();
				return _inst;
			}
		}
		
		#endregion
		
        #region Get Methods

		private DatCho GetCurrent(IDataReader reader, bool isDeep)
		{
			DatCho ret = new DatCho();
            ret.MaDatCho = (string)reader["MADATCHO"];
            ret.MaVe = (string)reader["MAVE"];
            ret.SLVe = (int)reader["SLVE"];
            ret.TrangThai = (TRANG_THAI)(int)reader["TRANGTHAI"];
            ret.PhiHuy = (decimal)reader["PHIHUY"];
            ret.TongTien = (decimal)reader["TONGTIEN"];
            ret.LoaiDatCho = (LOAI_DAT_CHO)(int)reader["LOAIDATCHO"];
            ret.LuotDatCho = (LUOT_DAT_CHO)(int)reader["LUOTDATCHO"];
			
			ret.ItemChiTietCBay = new ChiTietChuyenBay();
			if(isDeep)
			{
                ret.ItemChiTietCBay = ChiTietCBayDA.Inst.GetByPrimaryKey(isDeep, (int)reader["MACTCBAY"]);
			}
			else
                ret.ItemChiTietCBay.MaCTCBay = (int)reader["MACTCBAY"];
			
			ret.ItemHanhKhach = new HanhKhach();
			if(isDeep)
			{
                ret.ItemHanhKhach = HanhKhachDA.Inst.GetByPrimaryKey(isDeep, (string)reader["MAHK"]);
			}
			else
                ret.ItemHanhKhach.MaHanhKhach = (string)reader["MAHK"];
			
			
			return ret;
		}

		public List<DatCho> GetAll(bool isDeep)
		{
			DbCommand comm = GetCommand(_selectSql);
            using (Connection)
            {
                using (DbDataReader dr = comm.ExecuteReader())
                {
                    List<DatCho> ret = new List<DatCho>();
					while(dr.Read())
					{
						ret.Add(GetCurrent(dr, isDeep));
					}
					return ret;
                }
            }
		}
		
		public DatCho GetByPrimaryKey(bool isDeep, params object[] args)
		{
			DbCommand cmd = GetCommand(_primarSql);
			if(args.Length < 1)
			{
				throw new Exception();
			}
            SetParam(cmd, "@MADATCHO", args[0]);

            using (Connection)
            {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
					if(dr.Read())
					{
						return GetCurrent(dr, isDeep);
					}
					return null;
                }
            }
		}
        public List<DatCho> GetListDCByHK(string MaHK)
        {
            DbCommand comm = GetCommand("select * from DATCHO where MAHK='"+MaHK+"'");
            using (Connection)
            {
                using (DbDataReader dr = comm.ExecuteReader())
                {
                    List<DatCho> ret = new List<DatCho>();
                    while (dr.Read())
                    {
                        ret.Add(GetCurrent(dr, true));
                    }
                    return ret;
                }
            }
        }
        public decimal GetPhiHuyByMaVe(string MaVe)
        {
            string str = " select SUM(lv.PHIHUY) from DATCHO dc join CHITIETCBAY ct on dc.MACTCBAY=ct.MACTCBAY "
 + "join VE_GHE vg on ct.MAGHEVE=vg.MAGHEVE join LOAIVE lv on vg.MALOAIVE=lv.MALOAIVE "
 + "where MAVE='" + MaVe + "'";

            DbCommand cmd = GetCommand(str);
            using (Connection)
            {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        return Convert.ToDecimal(dr[0]);
                    }
                    return 0;
                }
            }
        }
        public List<DatCho> GetDatChoByMaVe(string MaVe)
        {
            string str = " select * from DATCHO dc "
 + "where MAVE='" + MaVe + "'";
            DbCommand cmd = GetCommand(str);
            using (Connection)
            {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
                    List<DatCho> ret = new List<DatCho>();
                    while (dr.Read())
                    {
                        ret.Add(GetCurrent(dr, true));
                    }
                    return ret;
                }
            }
        }
        public bool HuyVeByMaVe(string MaVe,decimal PhiHuy)
        {
            var lstDC = GetDatChoByMaVe(MaVe);
            foreach (var ob in lstDC)
            {
                ob.PhiHuy = PhiHuy;
                ob.TrangThai = TRANG_THAI.HUY;                
            }
            string connStr = ConfigurationManager.ConnectionStrings["QLVMBAY.ConnectionString"].ConnectionString;
            SqlConnection db = new SqlConnection(connStr);
            SqlTransaction transaction;

            db.Open();
            transaction = db.BeginTransaction();
            try
            {
                foreach (var ob in lstDC)
                {
                    var cmd1 = new SqlCommand(_updateSql, db, transaction);

                    SetParam(cmd1, "@MADATCHO", ob.MaDatCho);
                    SetParam(cmd1, "@MAVE", ob.MaVe);
                    SetParam(cmd1, "@SLVE", ob.SLVe);
                    SetParam(cmd1, "@TRANGTHAI", (int)ob.TrangThai);
                    SetParam(cmd1, "@PHIHUY", ob.PhiHuy);
                    SetParam(cmd1, "@TONGTIEN", ob.TongTien);
                    SetParam(cmd1, "@LOAIDATCHO", (int)ob.LoaiDatCho);
                    SetParam(cmd1, "@LUOTDATCHO", (int)ob.LuotDatCho);
                    SetParam(cmd1, "@MACTCBAY", ob.ItemChiTietCBay.MaCTCBay);
                    SetParam(cmd1, "@MAHK", ob.ItemHanhKhach.MaHanhKhach);
                    var cmd2 = new SqlCommand("update CHITIETCBAY set SLCONLAI=SLCONLAI +" + ob.SLVe + " where MACTCBAY=" + ob.ItemChiTietCBay.MaCTCBay + "", db, transaction);
                    cmd1.ExecuteNonQuery();
                    cmd2.ExecuteNonQuery();
                }
                transaction.Commit();
                db.Close();
                return true;
            }
            catch (SqlException sqlError)
            {
                transaction.Rollback();
                db.Close();
                return false;
            }
        }
        public bool ThemDatCho(List<DatCho> lstDatCho)
        {           
            string connStr = ConfigurationManager.ConnectionStrings["QLVMBAY.ConnectionString"].ConnectionString;
                SqlConnection db = new SqlConnection(connStr);
                SqlTransaction transaction;

                db.Open();
                transaction = db.BeginTransaction();
                try
                {
                    foreach (var ob in lstDatCho)
                    {
                        var cmd1 = new SqlCommand(_insertSql, db, transaction);

                        SetParam(cmd1, "@MADATCHO", ob.MaDatCho);
                        SetParam(cmd1, "@MAVE", ob.MaVe);
                        SetParam(cmd1, "@SLVE", ob.SLVe);
                        SetParam(cmd1, "@TRANGTHAI", (int)ob.TrangThai);
                        SetParam(cmd1, "@PHIHUY", ob.PhiHuy);
                        SetParam(cmd1, "@TONGTIEN", ob.TongTien);
                        SetParam(cmd1, "@LOAIDATCHO", (int)ob.LoaiDatCho);
                        SetParam(cmd1, "@LUOTDATCHO", (int)ob.LuotDatCho);
                        SetParam(cmd1, "@MACTCBAY", ob.ItemChiTietCBay.MaCTCBay);
                        SetParam(cmd1, "@MAHK", ob.ItemHanhKhach.MaHanhKhach);
                        var cmd2 = new SqlCommand("update CHITIETCBAY set SLCONLAI=SLCONLAI-" + ob.SLVe + " where MACTCBAY=" + ob.ItemChiTietCBay.MaCTCBay + "", db, transaction);                        
                        cmd1.ExecuteNonQuery();
                        cmd2.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    db.Close();
                    return true;
                }
                catch (SqlException sqlError)
                {
                    transaction.Rollback();
                    db.Close();
                    return false;
                }
                
 
        }
		public bool Insert(DatCho entity)
		{
			DbCommand cmd = GetCommand(_insertSql);
            SetParam(cmd, "@MADATCHO", entity.MaDatCho);
            SetParam(cmd, "@SLVE", entity.SLVe);
            SetParam(cmd, "@TRANGTHAI", (int)entity.TrangThai);
            SetParam(cmd, "@PHIHUY", entity.PhiHuy);
            SetParam(cmd, "@TONGTIEN", entity.TongTien);
            SetParam(cmd, "@LOAIDATCHO", (int)entity.LoaiDatCho);
            SetParam(cmd, "@LUOTDATCHO", (int)entity.LuotDatCho);
			SetParam(cmd, "@MACTCBAY", entity.ItemChiTietCBay.MaCTCBay);
			SetParam(cmd, "@MAHK", entity.ItemHanhKhach.MaHanhKhach);
			
            using (Connection)
            {
				int ret = cmd.ExecuteNonQuery();
				return ret > 0;
            }
		}
		
		public bool Update(DatCho entity)
		{
			DbCommand cmd = GetCommand(_updateSql);
            SetParam(cmd, "@MADATCHO", entity.MaDatCho);
            SetParam(cmd, "@SLVE", entity.SLVe);
            SetParam(cmd, "@TRANGTHAI",(int) entity.TrangThai);
            SetParam(cmd, "@PHIHUY", entity.PhiHuy);
            SetParam(cmd, "@TONGTIEN", entity.TongTien);
            SetParam(cmd, "@LOAIDATCHO", (int)entity.LoaiDatCho);
            SetParam(cmd, "@LUOTDATCHO", (int)entity.LuotDatCho);
			SetParam(cmd, "@MACTCBAY", entity.ItemChiTietCBay.MaCTCBay);
			SetParam(cmd, "@MAHK", entity.ItemHanhKhach.MaHanhKhach);
			
            using (Connection)
            {
				int ret = cmd.ExecuteNonQuery();
				return ret > 0;
            }
		}
		
		public bool Delete(DatCho entity)
		{
			DbCommand cmd = GetCommand(_deleteSql);
            SetParam(cmd, "@MADATCHO", entity.MaDatCho);

            using (Connection)
            {
				int ret = cmd.ExecuteNonQuery();
				return ret > 0;
            }
		}
		
		#endregion
    }
}


